package com.heh.mybatisplus.controller;

/**
 * @项目名： spring-emq
 * @包名： com.heh.mybatisplus.controller
 * @文件名: DatabaseCompare
 * @创建者: liufudong@szyh-smart.com
 * @创建时间: 2024/10/25 14:21
 * @描述：
 */

import com.fasterxml.jackson.annotation.JsonProperty;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;

import java.io.Serializable;
import java.sql.*;
import java.util.*;

public class DatabaseCompare {
    static List<String> tableNames = Arrays.asList(
            "classes",
            "client_log",
            "export_task",
            "i_apply_school",
            "i_area",
            "i_member",
            "i_member_change",
            "i_member_detail",
            "i_member_identity",
            "i_report",
            "i_report_template",
            "i_school",
            "i_school_change",
            "i_school_classes",
            "i_school_grade",
            "i_school_post",
            "i_workflow",
            "i_workflow_detail",
            "i_workflow_grade_class",
            "isys_log",
            "isys_menu",
            "isys_menu_button",
            "isys_role",
            "isys_role_menu",
            "isys_user",
            "isys_user_detail",
            "isys_user_district_permission",
            "isys_user_role",
            "isys_user_school_permission",
            "sys_app_package",
            "sys_dict",
            "sys_dict_data",
            "sys_log",
            "sys_menu",
            "sys_menu_button",
            "sys_role",
            "sys_role_menu",
            "sys_user",
            "sys_user_detail",
            "sys_user_role",
            "t_answer",
            "t_answer_filter_rules",
            "t_answer_filter_rules_member",
            "t_answer_filter_rules_record",
            "t_answer_hint",
            "t_answer_member",
            "t_answer_member_notice",
            "t_answer_member_process_data",
            "t_answer_member_reminder_log",
            "t_answer_member_result",
            "t_answer_question",
            "t_answer_question_option_statistics",
            "t_answer_question_statistics",
            "t_answer_rules",
            "t_answer_template",
            "t_answer_template_question",
            "t_area",
            "t_area_target_point",
            "t_bivariate_manage",
            "t_bivariate_manage_option_statistics",
            "t_bivariate_manage_record",
            "t_bivariate_manage_statistics",
            "t_bivariate_question_statistics",
            "t_bivariate_target_point",
            "t_city_target_point",
            "t_classes_target_point",
            "t_district_info",
            "t_district_target_point",
            "t_grade_target_point",
            "t_member",
            "t_member_copy1",
            "t_member_detail",
            "t_member_identity",
//            "t_member_process_statistics",
            "t_member_target_point",
            "t_message_report",
            "t_notice_task",
            "t_notice_task_user",
            "t_project",
            "t_project_target_point",
            "t_province_target_point",
            "t_question",
            "t_resource",
            "t_school",
            "t_school_answer_question_option_statistics",
            "t_school_answer_question_statistics",
            "t_school_classes",
            "t_school_grade",
            "t_school_post",
            "t_school_post_member",
            "t_school_target_point",
            "t_target",
            "t_tenant",
            "t_tenant_answer_template",
            "t_tenant_district_info",
            "t_type_target_point",
            "tsys_dict",
            "tsys_dict_data",
            "tsys_menu",
            "tsys_menu_button",
            "tsys_role",
            "tsys_role_menu",
            "tsys_user",
            "tsys_user_area_permission",
            "tsys_user_detail",
            "tsys_user_district_permission",
            "tsys_user_role",
            "tsys_user_school_permission"
    );

    static String DB_URL = "jdbc:mysql://rm-wz9i4ukx2q5hez3pavo.mysql.rds.aliyuncs.com:3306/";
    //    static String url2 = "jdbc:mysql://rm-wz9i4ukx2q5hez3pavo.mysql.rds.aliyuncs.com:3306/edu_yace";
    static String USER = "edu";
    static String PASS = "Szyh2024";

    public static void main(String[] args) {
        ObjectMapper objectMapper=new ObjectMapper();
        Map<String, List<ColumnInfo>> eduLuohuColumns = getColumnsFromDatabase("edu_luohu");
        Map<String, List<IndexInfo>> eduLuohuIndexes = getIndexesFromDatabase("edu_luohu");

        Map<String, List<ColumnInfo>> eduYaceColumns = getColumnsFromDatabase("edu_yace");
        Map<String, List<IndexInfo>> eduYaceIndexes = getIndexesFromDatabase("edu_yace");
        // 比较逻辑
        for (String table : tableNames) {
            List<ColumnInfo> luohuColumns = eduLuohuColumns.get(table);
            List<ColumnInfo> yaceColumns = eduYaceColumns.get(table);

            List<IndexInfo> luohuIndexes = eduLuohuIndexes.get(table);
            List<IndexInfo> yaceIndexes = eduYaceIndexes.get(table);

            // 对比表结构
            if (!Objects.equals(luohuColumns, yaceColumns)) {
                System.out.println("Table " + table + " 表结构不一样");
//                try {
//                    System.out.println("{1}:" + objectMapper.writeValueAsString(luohuColumns));
//                    System.out.println("{2}:" + objectMapper.writeValueAsString(yaceColumns));
//                } catch (JsonProcessingException e) {
//                    throw new RuntimeException(e);
//                }
            } else {
//                System.out.println("Table " + table + " structure equals.");
            }

            // 对比索引
            if (!Objects.equals(luohuIndexes, yaceIndexes)) {
//                try {
//                    System.out.println("{3}:" + objectMapper.writeValueAsString(luohuIndexes));
//                    System.out.println("{4}:" + objectMapper.writeValueAsString(yaceIndexes));
//                } catch (JsonProcessingException e) {
//                    throw new RuntimeException(e);
//                }
                System.out.println("Table " + table + " 表索引不一样");
            } else {
//                System.out.println("Table " + table + " indexes equals.");
            }
        }
    }

    private static Map<String, List<ColumnInfo>> getColumnsFromDatabase(String dbName) {
        Map<String, List<ColumnInfo>> columnsMap = new HashMap<>();
        try (Connection conn = DriverManager.getConnection(DB_URL + dbName, USER, PASS)) {
            for (String table : tableNames) {
                List<ColumnInfo> columns = new ArrayList<>();
                String query = "SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";
                try (PreparedStatement stmt = conn.prepareStatement(query)) {
                    stmt.setString(1, dbName);
                    stmt.setString(2, table);
                    ResultSet rs = stmt.executeQuery();
                    while (rs.next()) {
                        columns.add(new ColumnInfo(rs.getString("COLUMN_NAME"),
                                rs.getString("DATA_TYPE"),
                                rs.getString("IS_NULLABLE"),
                                rs.getString("COLUMN_DEFAULT")));
                    }
                }
                columnsMap.put(table, columns);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return columnsMap;
    }

    private static Map<String, List<IndexInfo>> getIndexesFromDatabase(String dbName) {
        Map<String, List<IndexInfo>> indexesMap = new HashMap<>();
        try (Connection conn = DriverManager.getConnection(DB_URL + dbName, USER, PASS)) {
            for (String table : tableNames) {
                List<IndexInfo> indexes = new ArrayList<>();
                String query = "SHOW INDEX FROM " + table;
                try (Statement stmt = conn.createStatement()) {
                    ResultSet rs = stmt.executeQuery(query);
                    while (rs.next()) {
                        indexes.add(new IndexInfo(rs.getString("Key_name"),
                                rs.getString("Column_name"),
                                rs.getInt("Seq_in_index")));
                    }
                }
                indexesMap.put(table, indexes);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return indexesMap;
    }

    // 列信息类
    static class ColumnInfo implements Serializable {
        String name;
        String dataType;
        String isNullable;
        String columnDefault;
        private static final long serialVersionUID = 1L;

        @JsonProperty("name")
        public String getName() {
            return name;
        }

        @JsonProperty("data_type")
        public String getDataType() {
            return dataType;
        }

        @JsonProperty("is_nullable")
        public String getIsNullable() {
            return isNullable;
        }

        @JsonProperty("column_default")
        public String getColumnDefault() {
            return columnDefault;
        }

        public ColumnInfo(String name, String dataType, String isNullable, String columnDefault) {
            this.name = name;
            this.dataType = dataType;
            this.isNullable = isNullable;
            this.columnDefault = columnDefault;
        }

        @Override
        public boolean equals(Object obj) {
            if (this == obj) return true;
            if (!(obj instanceof ColumnInfo)) return false;
            ColumnInfo other = (ColumnInfo) obj;
            return Objects.equals(name, other.name) &&
                    Objects.equals(dataType, other.dataType) &&
                    Objects.equals(isNullable, other.isNullable) &&
                    Objects.equals(columnDefault, other.columnDefault);
        }

        @Override
        public int hashCode() {
            return Objects.hash(name, dataType, isNullable, columnDefault);
        }
    }

    // 索引信息类
    static class IndexInfo implements Serializable {
        private static final long serialVersionUID = 1L;

        private String keyName;
        private String columnName;
        private int seqInIndex;

        public IndexInfo(String keyName, String columnName, int seqInIndex) {
            this.keyName = keyName;
            this.columnName = columnName;
            this.seqInIndex = seqInIndex;
        }

        @JsonProperty("key_name")
        public String getKeyName() {
            return keyName;
        }

        @JsonProperty("column_name")
        public String getColumnName() {
            return columnName;
        }

        @JsonProperty("seq_in_index")
        public int getSeqInIndex() {
            return seqInIndex;
        }

        @Override
        public boolean equals(Object obj) {
            if (this == obj) return true;
            if (!(obj instanceof IndexInfo)) return false;
            IndexInfo other = (IndexInfo) obj;
            return Objects.equals(keyName, other.keyName) &&
                    Objects.equals(columnName, other.columnName) &&
                    seqInIndex == other.seqInIndex;
        }

        @Override
        public int hashCode() {
            return Objects.hash(keyName, columnName, seqInIndex);
        }
    }
}